/*2012-7-9 saved
实现功能:
1.根据用户名遍历用户信息（UID PWD GID Uname）
2.根据输入用户名和服务器名 判断用户是否有权限访问服务器
3.根据GID 遍历输出该组的所有用户信息
author:mr
*/
    #include "sql_mr.h"
    #include <nss.h> //hua add
    #include <pwd.h> //hua add
    #include <mysql/mysql.h>
    #include <string.h>
    #include <stdlib.h>
    #include <stdio.h>
	#include<shadow.h>
    #include <iostream>
    using namespace std;
    /*定义了一些数据库连接需要的宏*/
    #define HOST "localhost"
    #define USERNAME "root"
    #define PASSWORD "root"
    #define DATABASE "xxx"


      struct passwdsend
{
	char pw_name[100];               // Username.
	char pw_passwd[2];              //Password.
	__uid_t pw_uid;              // User ID.
	__gid_t pw_gid;              // Group ID.
	char pw_gecos[100];              // Real name.
	char pw_dir[100];                // Home directory.
	char pw_shell[100];              // Shell program.
};
struct spwdsend
{
    char sp_namp[100];         // Login name
	char sp_pwdp[100];         // Encrypted password
	long sp_lstchg;        // Date of last change
	long sp_min;          //  Min #days between changes
	long sp_max;          //  Max #days between changes
	long sp_warn;         //  #days before pwd expires to warn user to change it
	long sp_inact;        //  #days after pwd expires until account is disabled
	long sp_expire;        // #days since 1970-01-01 until account is disabled
	unsigned long sp_flag; // Reserved
};


  /*根据用户名返回用户shandow密码*/
  void query_uspwd(char* uname,struct spwdsend *p) {
	cout<<uname<<endl;
      MYSQL my_connection; /*这是一个数据库连接*/
      int res; /*执行sql語句后的返回标志*/
      MYSQL_RES *res_ptr; /*指向查询结果的指针*/
      MYSQL_FIELD *field; /*字段结构指针*/
      MYSQL_ROW result_row; /*按行返回的查询信息*/
      int row, column;
      int i, j;
      char a[200]="select User_Info.UID,Pwd,GID,Uname from User_ID,User_Info where User_Info.UID=User_ID.UID and Uname='";
      strcat(a,uname);
      char b[10]="'";
      strcat(a,b);
      mysql_init(&my_connection);
      if (mysql_real_connect(&my_connection, HOST, USERNAME, PASSWORD, DATABASE,
              0, NULL, CLIENT_FOUND_ROWS)) {/*Connection success*/
           printf("数据库查询query_userspwd连接成功！\n");
           /*这句话是设置查询编码为utf8，这样支持中文*/
           mysql_query(&my_connection, "set names utf8");
           res = mysql_query(&my_connection, a);
          if (res) { /*执行失败了*/
              printf("Error：%s\n",mysql_error(&my_connection));
              mysql_close(&my_connection);
          } else { /*执行成功了*/
              res_ptr = mysql_store_result(&my_connection);

              /*如果结果不为空，就把结果print*/
              if (res_ptr)
              {

                  column = mysql_num_fields(res_ptr);
                  row = mysql_num_rows(res_ptr);
                  printf("查询到 %d 行 \n", row);

                  /*输出結果的字段名*/
                  for (i = 0; field = mysql_fetch_field(res_ptr); i++)
                     printf("%s\t", field->name);
                 printf("\n");

                 /*按行输出結果*/
                 for (i = 1; i <=row; i++) {
                     result_row = mysql_fetch_row(res_ptr);
                     for (j = 0; j < column; j++)
                     {
                          printf("%s\t", result_row[j]);//j列
                         // UID | Pwd | GID  | Uname

                     }
                     printf("\n");
			cout<<"数据库用户密码的直接查询结果为："<<result_row[1]<<endl;
			cout<<"这里是测试看的数据"<<endl;
			cout<<"总共查询到的字段数字是"<<column<<endl;
			for(int hua_ii=0;hua_ii<column;hua_ii++)
				cout<<result_row[hua_ii]<<endl;
			cout<<"测试结束"<<endl;
                    strcpy(p->sp_namp,result_row[3]);
                    strcpy(p->sp_pwdp,result_row[1]);
                    p->sp_lstchg=16528;
                    p->sp_min=0;
                    p->sp_max=99999;
                    p->sp_warn=99999;
                    p->sp_inact=99999;
                    p->sp_expire=99999;
                    p->sp_flag=99999;

                  //  cout<<p.pw_uid<<p.pw_passwd<<p.pw_gid<<p.pw_name<<endl;
                 }

             }


             mysql_close(&my_connection);
         }
     }
    // return p;
 }



  /*根据用户名返回User_ID , User_Info 中的所有信息，  完善数据库后应该返回struct结构体中的所有信息*/
  void query_user(char* uname,struct passwdsend *p) {
	 strcpy(p->pw_shell,"/bin/bash");
	char temp_hua_7_12[20]="/home/";
	strcat(temp_hua_7_12,uname);
	strcpy(p->pw_dir,temp_hua_7_12);

      MYSQL my_connection; /*这是一个数据库连接*/
      int res; /*执行sql語句后的返回标志*/
      MYSQL_RES *res_ptr; /*指向查询结果的指针*/
      MYSQL_FIELD *field; /*字段结构指针*/
      MYSQL_ROW result_row; /*按行返回的查询信息*/
      int row, column;
      int i, j;
      char a[200]="select User_Info.UID,Pwd,GID,Uname from User_ID,User_Info where User_Info.UID=User_ID.UID and Uname='";
      strcat(a,uname);
      char b[10]="'";
      strcat(a,b);
      mysql_init(&my_connection);
      if (mysql_real_connect(&my_connection, HOST, USERNAME, PASSWORD, DATABASE,
              0, NULL, CLIENT_FOUND_ROWS)) {/*Connection success*/
           printf("数据库查询query_user连接成功！\n");
           /*这句话是设置查询编码为utf8，这样支持中文*/
           mysql_query(&my_connection, "set names utf8");
           res = mysql_query(&my_connection, a);

          if (res) { /*执行失败了*/
              printf("Error：%s\n",mysql_error(&my_connection));
              mysql_close(&my_connection);
          } else { /*执行成功了*/
              res_ptr = mysql_store_result(&my_connection);

              /*如果结果不为空，就把结果print*/
              if (res_ptr)
              {

                  column = mysql_num_fields(res_ptr);
                  row = mysql_num_rows(res_ptr);
                  printf("查询到 %d 行 \n", row);

                  /*输出結果的字段名*/
                  /*输出結果的字段名*/
                  /*输出結果的字段名*/
                  for (i = 0; field = mysql_fetch_field(res_ptr); i++)
                     printf("%s\t", field->name);
                 printf("\n");

                 /*按行输出結果*/
                 for (i = 1; i <=row; i++) {
                     result_row = mysql_fetch_row(res_ptr);
                     for (j = 0; j < column; j++)
                     {
                          printf("%s\t", result_row[j]);//j列
                         // UID | Pwd | GID  | Uname

                     }
                     printf("\n");
			cout<<"数据库的直接查询结果为："<<result_row[0]<<endl;
                     p->pw_uid=atol(result_row[0]);
                     strcpy(p->pw_passwd,"x");
                     p->pw_gid=atol(result_row[2]);
                     strcpy(p->pw_name,result_row[3]);
                  //  cout<<p.pw_uid<<p.pw_passwd<<p.pw_gid<<p.pw_name<<endl;
                 }

             }


             mysql_close(&my_connection);
         }
     }
    // return p;
 }

void query_uid(char* uid,struct passwdsend *p) {
 strcpy(p->pw_shell,"/bin/bash");
        char temp_hua_7_12[20]="/home/";



      MYSQL my_connection; /*这是一个数据库连接*/
      int res; /*执行sql語句后的返回标志*/
      MYSQL_RES *res_ptr; /*指向查询结果的指针*/
      MYSQL_FIELD *field; /*字段结构指针*/
      MYSQL_ROW result_row; /*按行返回的查询信息*/
      int row, column;
      int i, j;
      char a[200]="select User_Info.UID,Pwd,GID,Uname from User_ID,User_Info where User_Info.UID=User_ID.UID and User_ID.UID='";

      strcat(a,uid);
      char b[10]="'";
      strcat(a,b);
cout<<"正在进行的UID SQL数据库查询SQL语句是"<<b<<endl;
      mysql_init(&my_connection);
      if (mysql_real_connect(&my_connection, HOST, USERNAME, PASSWORD, DATABASE,
              0, NULL, CLIENT_FOUND_ROWS)) {/*Connection success*/
           printf("数据库查询query_user连接成功！\n");
           /*这句话是设置查询编码为utf8，这样支持中文*/
           mysql_query(&my_connection, "set names utf8");
           res = mysql_query(&my_connection, a);
	 cout<<a<<endl;
          if (res) { /*执行失败了*/
              printf("Error：%s\n",mysql_error(&my_connection));
              mysql_close(&my_connection);
          } else { /*执行成功了*/
              res_ptr = mysql_store_result(&my_connection);

              /*如果结果不为空，就把结果print*/
              if (res_ptr)
              {

                  column = mysql_num_fields(res_ptr);
                  row = mysql_num_rows(res_ptr);
                  printf("查询到 %d 行 \n", row);

                  /*输出結果的字段名*/
                  for (i = 0; field = mysql_fetch_field(res_ptr); i++)
                     printf("%s\t", field->name);
                 printf("\n");

                 /*按行输出結果*/
                 for (i = 1; i <=row; i++) {
                     result_row = mysql_fetch_row(res_ptr);
                     for (j = 0; j < column; j++)
                     {
                          printf("%s\t", result_row[j]);//j列
                         // UID | Pwd | GID  | Uname

                     }
                     printf("\n");
			cout<<"UID是："<<result_row[0]<<endl;



                     //p->pw_uid=atol (result_row[0]);
                     p->pw_uid=atol (result_row[0]);
                     strcpy(p->pw_passwd,"x");
                     p->pw_gid=atol(result_row[2]);
                     strcpy(p->pw_name,result_row[3]);
			strcat(temp_hua_7_12,p->pw_name);
		 	strcpy(p->pw_dir,temp_hua_7_12);

                  //  cout<<p.pw_uid<<p.pw_passwd<<p.pw_gid<<p.pw_name<<endl;
                 }

             }


             mysql_close(&my_connection);
         }
     }
    // return p;
 }

/*
*  根据用户名 和 服务器名称来判断用户是否有权限登录该服务器
*  返回1 =====true;* -1===false;* 2 ===no user;* 3====no server
*/
int query_name(char *uname, char * sname){
cout<<"这里将显示传入验证用户是否可以登录服务器的用户名和服务器名称"<<uname<<sname<<endl;
char c[100];
char parent[100];
char gpoint[100];
char spoint[100];
char a2[100]="select S_Point from Server_Info where Sname='";
strcat(a2,sname);
char b2[100]="'";
strcat(a2,b2);
MYSQL my_connection,my_connection1,my_connection2;
int res,res1,res2;
MYSQL_RES *res_ptr,*res_ptr1,*res_ptr2;
      //MYSQL_FIELD *field,*field1,*field2;
MYSQL_ROW result_row,result_row1,result_row2;

      mysql_init(&my_connection);
      if (mysql_real_connect(&my_connection, HOST, USERNAME, PASSWORD, DATABASE,
              0, NULL, CLIENT_FOUND_ROWS))
    {

           mysql_query(&my_connection, "set names utf8");
           char a3[200]="select G_Point from Group_Info, User_Info, User_ID where Group_Info.GID=User_Info.GID and User_Info.UID=User_ID.UID and 			Uname='";
           strcat(a3,uname);
           char b3[10]="'";
           strcat(a3,b3);
           res = mysql_query(&my_connection, a3);
		cout<<a3<<endl;//for test
           if (res)
           {
                  printf("Error：%s\n",mysql_error(&my_connection));
                  mysql_close(&my_connection);
            } else
            {
                   res_ptr = mysql_store_result(&my_connection);
                    if (res_ptr)
                    {

                         result_row = mysql_fetch_row(res_ptr);
                         if(0==mysql_num_rows(res_ptr ))
                         {
                             return 2;
                        }
                            else
                                strcpy(gpoint,result_row[0]);
                     }
                    mysql_close(&my_connection);
            }

       } else cout<<mysql_error(&my_connection);
     mysql_init(&my_connection1);
	 if (mysql_real_connect(&my_connection1, HOST, USERNAME, PASSWORD, DATABASE,
              0, NULL, CLIENT_FOUND_ROWS)) {
         mysql_query(&my_connection1, "set names utf8");
	     res1=mysql_query(&my_connection1,a2);
         if (res1) {
             printf("Error：%s\n",mysql_error(&my_connection1));
              mysql_close(&my_connection1);
          } else {
		        res_ptr1 = mysql_store_result(&my_connection1);
                if (res_ptr1) {
                    result_row1 = mysql_fetch_row(res_ptr1);
                    if(0==mysql_num_rows(res_ptr1)) return 3;
 		            else{
                       strcpy(spoint,result_row1[0]);
                        strcpy(c,spoint);
                        }
                    mysql_close(&my_connection1);
               }
            }
         if(!strcmp(spoint,gpoint)){
            return 1;
         }
         else{
            mysql_init(&my_connection2);
            if (mysql_real_connect(&my_connection2, HOST, USERNAME, PASSWORD, DATABASE,
	              0, NULL, CLIENT_FOUND_ROWS)) {
            bool flag=true;
            int a=1;
            while(flag&&atoi(c)!=-1){
         //mysql_query(&my_connection2, "set names utf8");
                char query3[100]="select Parent from Point where Child='";
                char q[10];
                strcpy(q,c);
                char p[10]="'";
                strcat(query3,q);
                strcat(query3,p);
                res2 = mysql_query(&my_connection2, query3);
                if (res2){
                    printf("Error：%s\n",mysql_error(&my_connection2));
                    break;
                    mysql_close(&my_connection2);
                } else{
                    res_ptr2 = mysql_store_result(&my_connection2);
                    if (res_ptr2){
                        if(0==mysql_num_rows(res_ptr2))
                        cout<<"wrong1"<<endl;
                        else{
                            result_row2= mysql_fetch_row(res_ptr2);
                            strcpy(parent,result_row2[0]);
                            if(atoi(parent)==atoi(gpoint)) flag=false;
                            else{ strcpy(c,parent);
                            }
                        }
                    }
                   }
              }
            mysql_close(&my_connection2);
            if(flag==false) return 1;
            else return -1;
	 }
    }
}
}
void search_members(int Group_ID)   //遍历组中所有用户的信息
{
    MYSQL my_connection,my_connection1,my_connection2;
    int res,res1,res2;
    MYSQL_RES *res_ptr,*res_ptr1,*res_ptr2;
    MYSQL_FIELD *field,*field1,*field2;
    MYSQL_ROW result_row,result_row1,result_row2;
    mysql_init(&my_connection);
    if (mysql_real_connect(&my_connection, HOST, USERNAME, PASSWORD, DATABASE,
              0, NULL, CLIENT_FOUND_ROWS))
    {/*连接成功*/

    printf("数据库执行search_members连接成功！\n");
    char q[300]="select User_ID.UID,Pwd,Uname from User_ID,User_Info,Group_Info where User_ID.UID=User_Info.UID and User_Info.GID=Group_Info.GID and Group_Info.GID='";
    char id[10];
    sprintf(id,"%d",Group_ID);
    strcat(q,id);
    char p[10]="'";
    strcat(q,p);
    res = mysql_query(&my_connection, q);
    if(res){
    printf("Error %s",mysql_error(&my_connection));
    mysql_close(&my_connection);
    }
    else
    {
            res_ptr=mysql_store_result(&my_connection);
            if(res_ptr)
            {
                    if(0==mysql_num_rows(res_ptr)) cout<<"GID输入错误，该组不存在！";
                    else
                    {
                        int column = mysql_num_fields(res_ptr);
                        int row = mysql_num_rows(res_ptr);
                        printf("查询到 %d 行 \n", row);

                        /*输出結果的字段名*/
                        for (int i = 0; field = mysql_fetch_field(res_ptr); i++)
                            printf("%s\t", field->name);
                        printf("\n");
                        /*按行输出結果*/
                        for (int i = 1; i <=row; i++)
                        {
                            result_row = mysql_fetch_row(res_ptr);
                            for (int j = 0; j < column; j++)
                                printf("%s\t", result_row[j]);
                            printf("\n");
                        }
                  }
            }
        }
    }
}
/*这个函数用来执行传入的sql語句*/
  void exe_sql(char* sql) {

      MYSQL my_connection; /*这是一个数据库连接*/
      int res; /*执行sql語句后的返回标志*/
      mysql_init(&my_connection);
      if (mysql_real_connect(&my_connection, HOST, USERNAME, PASSWORD, DATABASE,
              0, NULL, CLIENT_FOUND_ROWS)) {/*连接成功*/

          printf("数据库执行exe_sql连接成功！\n");

          /*这句话是设置查询编码为utf8，这样支持中文*/
          mysql_query(&my_connection, "set names utf8");

          /*下面这句话就是用mysql_query函数来执行我们刚刚传入的sql語句，         这会返回一个int值，如果为0，证明語句执行成功*/
          res = mysql_query(&my_connection, sql);

          if (res) {/*现在就代表执行失败了*/
              printf("Error： mysql_query !\n");
              /*不要忘了关闭连接*/
              mysql_close(&my_connection);
          } else {/*现在就代表执行成功了*/
              /*mysql_affected_rows会返回执行sql后影响的行数*/
              printf("%llu 行受到影响！\n\n", mysql_affected_rows(&my_connection));
              /*不要忘了关闭连接*/
              mysql_close(&my_connection);
          }

      } else {
          /*数据库连接失败*/
          printf("数据库执行exe_sql连接失败！\n");
      }
  }
